import java.util.ArrayList;
import java.util.Collections;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;

public class Parser {
	
	public String replaceLiterals(String sqlQuery)	
	{
		sqlQuery = sqlQuery.replace("''", "");
		sqlQuery = sqlQuery.replaceAll(":\\d{1,}", "@BINDING");
		sqlQuery = sqlQuery.replaceAll("\'[^\']{1,}\'", "@LITERAL");
		sqlQuery = sqlQuery.replaceAll("(\\W{1,})(\\d{1,})", "$1@NUMBER");
		return sqlQuery;
	}
	
	public Query parseSelectToken(String query, Query resQuery)
	{	
		resQuery.addQuery(query);
		
		Pattern selectTokenPattern = Pattern.compile("select (.*?) from", Pattern.CASE_INSENSITIVE);
		Matcher selectMatcher = selectTokenPattern.matcher(query);
		String concatSplitter = "\\|\\|";
		String splitter = ",";
		ArrayList<String> tempResult = new ArrayList<String>();	
		ArrayList<String> result = new ArrayList<String>();	
		
		while(selectMatcher.find())
		{
			String group1 = selectMatcher.group(1);
			
			if(group1.contains("SELECT"))
			{
				/**
				 * handle case:
				 * SELECT (select so_id from trees where rownum < 2) AS object_id, 
				 * s.object_id AS value 
				 * FROM nc_objects s 
				 */
				
				int regionStart = selectMatcher.start(1)+group1.indexOf("SELECT");	
				int regionEnd;
				
				{
					int i = regionStart;
					
					int leftBracketsCount = 1;
					int rightBracketsCount = 0;
					int selectCount = 0;
					int fromCount = 0;

					int indexOfLastRightBracket = 0;
					
					while(leftBracketsCount != rightBracketsCount || selectCount != fromCount)
					{
						indexOfLastRightBracket = query.indexOf(')', i);
							
						if(indexOfLastRightBracket >= 0)
						{
							String substring = query.substring(i, indexOfLastRightBracket);
							rightBracketsCount += 1;
							leftBracketsCount += substring.split("\\(").length - 1;
							selectCount += substring.split("SELECT").length - 1;
							fromCount += substring.split("FROM").length - 1;
						}
						else break;
						
						i = indexOfLastRightBracket + 1;
					}	
					
					regionEnd = i-1;
				}
				
				String subquery = query.substring(regionStart, regionEnd);	
				resQuery = parseSelectToken(subquery, resQuery);
				
				query = query.replace("(" + subquery + ")", "@SUBQUERY");
				return parseSelectToken(query, resQuery);
			}
				
			String[] buffer = group1.split(concatSplitter);
			for(int i=0; i<buffer.length; i++)
			{
				Collections.addAll(tempResult,buffer[i].split(splitter));
			}
		}
		
		resQuery.addResults(mergeBrackets(tempResult));
		
		return resQuery;
	}
	
	public ArrayList<String> mergeBrackets(ArrayList<String> tempResult)
	{
		ArrayList<String> result = new ArrayList<String>();
		String str1 = "";
		
		for(int i=0, j=0; i<tempResult.size(); i++, j=i)
		{
			if(tempResult.get(i)==null)
				continue;

			
			int leftBracketCounter = 0;
			int rightBracketCounter = 0;
			
			while(i<tempResult.size())
			{
				int indexOfLastLeftBracket = 0;
				int indexOfLastRightBracket = 0;
				
				do
				{
					str1 = tempResult.get(j);
					indexOfLastLeftBracket = tempResult.get(j).indexOf('(', indexOfLastLeftBracket);
					if(indexOfLastLeftBracket >= 0)
					{
						leftBracketCounter++;
						indexOfLastLeftBracket++;
					}
				} while(indexOfLastLeftBracket >= 0 && indexOfLastLeftBracket+1<tempResult.get(j).length());
				
				do
				{
					indexOfLastRightBracket = tempResult.get(j).indexOf(')', indexOfLastRightBracket);
					if(indexOfLastRightBracket >= 0)
					{
						rightBracketCounter++;
						indexOfLastRightBracket++;
					}
					
				} while(indexOfLastRightBracket >= 0 && indexOfLastRightBracket+1<tempResult.get(j).length());				
								
				if(leftBracketCounter == rightBracketCounter)
				{
					if(i!=j)
					{
						tempResult.set(i, tempResult.get(i) + "," + tempResult.get(j));
						tempResult.remove(j);
					}
					
					result.add(tempResult.get(i));
						break;
				}
				else
				{
				
					if(i!=j)
					{
						tempResult.set(i, tempResult.get(i) + "," + tempResult.get(j));
						tempResult.remove(j);
					}
					j = i+1;
				}
			}
		}			
		
		return result;
	}
	
	public String deleteComments (String query)
	{
		return query.replaceAll("/\\*.*?\\*/|--.*?\n","");		
	}
	
	public String trim(String query)
	{
		return query.replaceAll(" {1,}( |\\)|,)|(\\() {1,}","$1$2").replaceAll("\n|\r|\n\r", "");
	}
	
	public ArrayList<String> parseFromToken(String query)
	{
		Pattern fromTokenPattern = Pattern.compile("from (.*?) ??(\\(*?select|;|start|where|group by|order by)", Pattern.CASE_INSENSITIVE);
		Pattern joinTokenPattern = Pattern.	compile("(left|right|inner)? join|on|and", Pattern.CASE_INSENSITIVE);
		Matcher fromMatcher = fromTokenPattern.matcher(query);
		String splitter = ",";
		ArrayList<String> result = new ArrayList<String>();
		while(fromMatcher.find())
		{
			String group = fromMatcher.group(1);
			Matcher joinMatcher = joinTokenPattern.matcher(group);			
			Collections.addAll(result, joinMatcher.replaceAll(splitter).split(splitter));
		}
		
		return result;
	}
	
	public ArrayList<String> parseGroupByToken(String query)
	{
		Pattern groupByTokenPattern = Pattern.compile("(group by .*?)(order by|;|\\))", Pattern.CASE_INSENSITIVE);
		Matcher groupByMatcher = groupByTokenPattern.matcher(query);
		ArrayList<String> result = new ArrayList<String>();
		while(groupByMatcher.find())
		{
			result.add(groupByMatcher.group(1));
		}
		
		return result;
	}
	
	public ArrayList<String> parseWithAndUnionToken(String query)
	{
		ArrayList<String> result = new ArrayList<String>();
		
		//parse WITH query		
		Pattern withTokenPattern = Pattern.compile("((START )?WITH ([A-Z](\\w|$|#|_){1,29} AS \\(.+\\),?)+) SELECT");
		Pattern withSubqueryTokenPattern = Pattern.compile("AS \\((.*?)\\),");
		Matcher withMatcher = withTokenPattern.matcher(query);
		while(withMatcher.find())
		{
			String group1 = withMatcher.group(1);
			
			//skip START WITH clause of hierarchical queries 
			if(group1.startsWith("START WITH"))
				continue;
				
			Matcher withSubqueyMatcher = withSubqueryTokenPattern.matcher(group1  + ",");
			
			while(withSubqueyMatcher.find())
			{
				result.add(withSubqueyMatcher.group(1));
			}
			
			query = query.replace(group1, "");
		}
		
		Pattern unionTokenPattern = Pattern.compile("(.*?)UNION (ALL)?(.*?)");
		Matcher unionMatcher = unionTokenPattern.matcher(query);
		boolean unionNotFound = true;
		
		while(unionMatcher.find())
		{
			unionNotFound = false;
			result.add(unionMatcher.group(1));
			result.add(unionMatcher.group(3));
		}
		
		if(unionNotFound)
			result.add(query);
		
		return result;
	}
	
	public ArrayList<String> parseWhereToken(String query)
	{
		Pattern whereTokenPattern = Pattern.compile("where (.*?) ??(\\(*?select|;|order by|group by|with)", Pattern.CASE_INSENSITIVE);
		Matcher whereMatcher = whereTokenPattern.matcher(query);
		String andSplitter = " AND ";
		String orSplitter = " OR ";
		ArrayList<String> result = new ArrayList<String>();
		while(whereMatcher.find())
		{
			String[] andSplitted = whereMatcher.group(1).split(andSplitter);
			for(int i=0; i<andSplitted.length; i++)
			{
				Collections.addAll(result, andSplitted[i].split(orSplitter));
			}			
		}		
		return result;
	}
	
	public ArrayList<String> parseHierarhyToken(String query)
	{
		Pattern hierarhyTokenPattern = Pattern.compile("(start with .*?) (connect by (prior)? .*?)(;|\\)|where)", Pattern.CASE_INSENSITIVE);
		Matcher hierarhyMatcher = hierarhyTokenPattern.matcher(query);
		ArrayList<String> result = new ArrayList<String>();
		while(hierarhyMatcher.find())
		{
			if(hierarhyMatcher.group(1)!=null) result.add(hierarhyMatcher.group(1));
			if(hierarhyMatcher.group(2)!=null) result.add(hierarhyMatcher.group(2));	
		}		
		return result;
	}
	
	public ArrayList<String> removeAliases(ArrayList<String> tokens)
	{
		ArrayList<String> result = new ArrayList<String>();		
		
		for(String token : tokens)
		{
			String buffer = token.trim();
			buffer = buffer.replaceAll("^([A-Z](\\w|$|#|_){1,29}\\.[A-Z](\\w|$|#|_){1,29}) [A-Z](\\w|$|#|_){1,29}$", "$1");
			buffer = buffer.replaceAll("[A-Z](\\w|$|#|_){0,29}\\.(([A-Z](\\w|$|#|_){1,29})|\\*)", "$2");
			buffer = buffer.replaceAll("([A-Z](\\w|$|#|_){1,29}\\)*?) AS ([A-Z](\\w|$|#|_){1,29})", "$1");
			buffer = buffer.replaceAll("\\) [A-Z](\\w|$|#|_){1,29}", ")");
			buffer = buffer.replaceAll("WHERE .*", "");
			
			int k = StringUtils.countMatches(buffer, ")");
			int n = StringUtils.countMatches(buffer, "(");
			if(k>n)
			{
				buffer = StringUtils.reverse(buffer);
				while(k!=n)
				{
					buffer = buffer.replaceFirst("\\)", "");
					k--;
				}				
				buffer = StringUtils.reverse(buffer);
			}
			else if (k<n)
			{
				while(k!=n)
				{
					buffer = buffer.replaceFirst("\\(", "");
					n--;
				}
			}			
			
			if(StringUtils.startsWith(buffer, "(") && StringUtils.endsWith(buffer, ")"))
				buffer = buffer.substring(1, buffer.length()-1);

			result.add(buffer);
		}
		
		return result;
	}
	
	public ArrayList<String> removeDuplicates(ArrayList<String> tokens)
	{
		boolean wasReplaced;
		ArrayList<String> result = new ArrayList<String>();
		String buffer;
		
		for(String token : tokens)
		{
			wasReplaced = false;
			buffer = token;
			while(wasReplaced)
			{
				buffer = token.replaceAll("(@(NUMBER, )+(@NUMBER)", "$2");
				buffer = token.replaceAll("(@LITERAL, )+(@LITERAL)", "$2");
				buffer = token.replaceAll("(@BINDING, )+(@BINDING)", "$2");
				wasReplaced = true;
			}
			
			result.add(buffer);
		}
		
		return result;
	}
}